USE HierarchyIdDemo
GO

-- get departments 2 levels beneath operations
-- SelfJoin.Org version, need to recurse JOINs for each level
-- so number of levels is fixed in the query
SELECT Parents.DepartmentName Parent,
		GrandChildren.DepartmentName GrandChild
	FROM SelfJoin.Org Parents
		JOIN SelfJoin.Org Children
			ON Parents.DepartmentID = Children.ParentID
		JOIN SelfJoin.Org GrandChildren
			ON Children.DepartmentID = GrandChildren.ParentID
	WHERE Parents.DepartmentID = 2
GO

-- get departments 2 levels beneath operations
-- hierarchyid version.
	SELECT Parents.DepartmentName Parent,
			Descendents.DepartmentName GrandChild
		FROM HierarchyId.Org Parents
			JOIN HierarchyId.Org Descendents
				ON Parents.Node.IsDescendant(Descendents.Node) = 1
					AND Descendents.Node.GetLevel() - Parents.Node.GetLevel() = 2
		WHERE Parents.DepartmentID = 2
GO

-- get department 2 levels above parking
-- SelfJoin.Org version, again number of levels is fixed
-- in the query
	SELECT Parents.DepartmentName Parent,
			GrandChildren.DepartmentName GrandChild
		FROM SelfJoin.Org Parents
			JOIN SelfJoin.Org Children
				ON Parents.DepartmentID = Children.ParentID
			JOIN SelfJoin.Org GrandChildren
				ON Children.DepartmentID = GrandChildren.ParentID
		WHERE GrandChildren.DepartmentID = 15
GO

-- get department 2 levels above parking
-- hierarchyid version.
SELECT Parents.DepartmentName Parent,
		Descendents.DepartmentName GrandChild
	FROM HierarchyId.Org Parents
		JOIN HierarchyId.Org Descendents
			ON Descendents.Node.GetAncestor(2) = Parents.Node
	WHERE Descendents.DepartmentID = 15
GO